﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MatriculaLib.Basic;

namespace MatriculaLib.Repositorio
{
    public class RepMatricula
    {
        private SqlConnection conexao;
        private string stringConexao = @"Data Source=VITOR-PC\SQLEXPRESS;Initial Catalog=Escola; user id=sa; pwd=jva;";
        private static RepMatricula instancia;

        private RepMatricula()
        {
        }


        public static RepMatricula getInstancia()
        {
            if (instancia == null)
            {
                instancia = new RepMatricula();
                return instancia;
            }
            return instancia;
        }


        public List<string> serie()
        {
            try
            {
                conexao = new SqlConnection(stringConexao);
                List<string> lista = new List<string>();
                SqlCommand sql = new SqlCommand("select * from Serie order by DescSerie", conexao);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    string serie;
                    serie = dr["DescSerie"].ToString();
                    lista.Add(serie);
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public int codSerie(string serie)
        {
            try
            {
                conexao = new SqlConnection(stringConexao);
                int codSerie = 0;
                SqlCommand sql = new SqlCommand("select CodSerie from Serie where DescSerie = @DescSerie", conexao);

                conexao.Open();

                sql.Parameters.AddWithValue("@DescSerie", serie);

                SqlDataReader dr = sql.ExecuteReader();
                while (dr.Read())
                {
                    codSerie = Convert.ToInt32(dr["CodSerie"]);
                }
                return codSerie;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<string> turma()
        {
            try
            {
                conexao = new SqlConnection(stringConexao);
                List<string> lista = new List<string>();
                SqlCommand sql = new SqlCommand("select * from turma order by DescTurma", conexao);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    string turma;
                    turma = dr["DescTurma"].ToString();
                    lista.Add(turma);
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public int codTurma(string turma)
        {
            try
            {
                conexao = new SqlConnection(stringConexao);
                int codTurma = 0;
                SqlCommand sql = new SqlCommand("select CodTurma from Turma where DescTurma = @DescTurma", conexao);

                conexao.Open();

                sql.Parameters.AddWithValue("@DescTurma", turma);

                SqlDataReader dr = sql.ExecuteReader();
                while (dr.Read())
                {
                    codTurma = Convert.ToInt32(dr["CodTurma"]);
                }
                return codTurma;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public int matAluno(string aluno)
        {
            int mat=0;
            try
            {
                conexao = new SqlConnection(stringConexao);
                SqlCommand sql = new SqlCommand("select MatAluno from Aluno where NomeAluno = @NomeAluno", conexao);

                conexao.Open();

                sql.Parameters.AddWithValue("@NomeAluno", aluno);

                SqlDataReader dr = sql.ExecuteReader();
                while (dr.Read())
                {
                    mat = Convert.ToInt32(dr["MatAluno"]);
                }
                return mat;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<string> turno(string turma)
        {

            conexao = new SqlConnection(stringConexao);
            try
            {
                List<string> lista = new List<string>();
                SqlCommand sql = new SqlCommand("select Turno from turma where DescTurma = @DescTurma", conexao);

                sql.Parameters.AddWithValue("@DescTurma", turma);

                conexao.Open();

                sql.ExecuteNonQuery();

                SqlDataReader dr = sql.ExecuteReader();
                while (dr.Read())
                {
                    string turno;
                    turno = dr["Turno"].ToString();
                    lista.Add(turno);
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public void matricula(Matricula matricula)
        {

            conexao = new SqlConnection(stringConexao);
            try
            {
                List<string> lista = new List<string>();
                SqlCommand sql = new SqlCommand("insert into Matricula Values(@Data, 2013002, @MatAluno, @CodSerie, @CodTurma)", conexao);

                sql.Parameters.AddWithValue("@Data", matricula.Data);
                sql.Parameters.AddWithValue("@MatAluno", matricula.MatAluno);
                sql.Parameters.AddWithValue("@CodSerie", matricula.CodSerie);
                sql.Parameters.AddWithValue("@CodTurma", matricula.CodTurma);

                conexao.Open();

                sql.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<Aluno> PesquisarAluno(string nome, string matricula)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Aluno> lista = new List<Aluno>();
                SqlCommand sql = new SqlCommand("select * from Aluno where NomeAluno like @NomeAluno and MatAluno like @Matricula order by MatAluno", conexao);
                sql.Parameters.AddWithValue("@NomeAluno", nome + '%');
                sql.Parameters.AddWithValue("@Matricula", matricula + '%');

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Aluno aluno = new Aluno();
                    aluno.Matricula = Convert.ToInt32(dr["MatAluno"]);
                    aluno.Nome = dr["NomeAluno"].ToString();
                    aluno.Endereco = dr["Endereço"].ToString();
                    aluno.Email = dr["Email"].ToString();
                    aluno.Telefone = dr["Telefone"].ToString();
                    aluno.Sexo = dr["Sexo"].ToString();
                    lista.Add(aluno);
                }
                return lista;
            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public List<Responsavel> pesquisarResponsavel(int matricula)
        {
            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Responsavel> lista = new List<Responsavel>();
                SqlCommand sql = new SqlCommand("select r.CodResp, r.NomeResp, r.Endereço, r.Telefone, r.Email, r.Sexo, r.Filiação, r.Categoria, a.MatAluno from Responsavel r inner join Aluno_Responsavel on r.CodResp=Aluno_Responsavel.CodResp inner join Aluno a on a.MatAluno=Aluno_Responsavel.MatAluno where Aluno_Responsavel.MatAluno=@MatAluno", conexao);

                sql.Parameters.AddWithValue("@MatAluno", matricula);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Responsavel responsavel = new Responsavel();
                    responsavel.CodResp1 = Convert.ToInt32(dr["CodResp"]);
                    responsavel.Nome = dr["NomeResp"].ToString();
                    responsavel.Endereco = dr["Endereço"].ToString();
                    responsavel.Telefone = dr["Telefone"].ToString();
                    responsavel.Email = dr["Email"].ToString();
                    responsavel.Sexo = dr["Sexo"].ToString();
                    responsavel.Filiacao = dr["Filiação"].ToString();
                    responsavel.Categoria = dr["Categoria"].ToString();
                    lista.Add(responsavel);
                }
                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<Matricula> PesquisarMatricula(int matricula)
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Matricula> lista = new List<Matricula>();
                SqlCommand sql = new SqlCommand("select s.DescSerie, t.DescTurma, t.Turno from Matricula m inner join Serie s on m.CodSerie=s.CodSerie inner join turma t on m.CodTurma=t.CodTurma where m.MatAluno=@MatAluno", conexao);
                sql.Parameters.AddWithValue("@MatAluno", matricula);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Matricula mat = new Matricula();
                    mat.Serie = dr["DescSerie"].ToString();
                    mat.Turma = dr["DescTurma"].ToString();
                    mat.Turno = dr["Turno"].ToString();
                    lista.Add(mat);
                }
                return lista;
            }
            catch (Exception e)
            {

                throw e;
            }

            finally
            {
                conexao.Dispose();
            }
        }

        public List<Matricula> listar()
        {

            conexao = new SqlConnection(stringConexao);

            try
            {
                List<Matricula> lista = new List<Matricula>();
                SqlCommand sql = new SqlCommand("select * from Matricula order by NumMat", conexao);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Matricula matricula = new Matricula();
                    matricula.NumMat = Convert.ToInt32(dr["NumMat"]);
                    matricula.MatAluno = Convert.ToInt32(dr["MatAluno"]);
                    lista.Add(matricula);
                }
                return lista;

            }
            catch (Exception e)
            {

                throw e;
            }


            finally
            {

                conexao.Dispose();

            }
        }

        public List<Quantidade> serie_turma(Matricula matricula)
        {
            conexao = new SqlConnection(stringConexao);
            try
            {
                int q = 12;
                List<Quantidade> lista = new List<Quantidade>(); ;

                SqlCommand sql = new SqlCommand("select QtdAlunosMax from Matricula m inner join Serie s on s.CodSerie = m.CodSerie inner join turma t on t.CodTurma=m.CodTurma where s.DescSerie = @DescSerie and t.DescTurma = @DescTurma", conexao);
                sql.Parameters.AddWithValue("@DescSerie", matricula.Serie);
                sql.Parameters.AddWithValue("@DescTurma", matricula.Turma);

                conexao.Open();

                SqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Quantidade qtd = new Quantidade();
                    qtd.QtdMax = Convert.ToInt32(dr["QtdAlunosMax"]);
                    lista.Add(qtd);
                }
                
                conexao.Dispose();

                conexao = new SqlConnection(stringConexao);
                SqlCommand sql2 = new SqlCommand(" update Serie_Turma set QtdAlunos=QtdAlunos+1 where CodSerie = @CodSerie and CodTurma = @CodTurma", conexao);
                
                sql2.Parameters.AddWithValue("@CodSerie", matricula.CodSerie);
                sql2.Parameters.AddWithValue("@CodTurma", matricula.CodTurma);
                conexao.Open();
                sql2.ExecuteNonQuery();

                return lista;

            }
            catch (Exception ex)
            {
                throw ex;
            }

            finally
            {
                conexao.Dispose();
            }
        }
    }
}


